package com.solt.prism.core.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.solt.prism.dao.imp.SqlConnectionPool;

public class ConverSqlToSqlServer {
	
	public static void convertSectionTableToTabTable () throws SQLException {
		String sql = "SELECT id, title FROM jos_sections";
		Connection sqlConnection = SqlConnectionPool.getConnection();
		Statement stat = sqlConnection.createStatement();
		ResultSet re = stat.executeQuery(sql);
		
		String sqlServer = "INSERT INTO Tab (TabID, name) VALUES (?,?)  ON DUPLICATE KEY UPDATE name=?;";
		Connection sqlServerConnection = SqlConnectionPool.getSqlServerConnection();
		PreparedStatement prepst = sqlServerConnection.prepareStatement(sqlServer);
		while (re.next()) {
			int sectionId = re.getInt("id");
			String sectionTitle = re.getString("title");
			
			prepst.setInt(1, sectionId);
			prepst.setString(2, sectionTitle);
			prepst.setString(3, sectionTitle);
			prepst.executeUpdate();
		}
	}
	
	
	public static void convertCategoryTable () throws SQLException {
		String sql = "SELECT id, title, section FROM jos_categories";
		Connection sqlConnection = SqlConnectionPool.getConnection();
		Statement stat = sqlConnection.createStatement();
		ResultSet re = stat.executeQuery(sql);
		
		String sqlServer = "INSERT INTO Category (CategoryID, title) values (?,?)";
		Connection sqlServerConnection = SqlConnectionPool.getSqlServerConnection();
		PreparedStatement prepst = sqlServerConnection.prepareStatement(sqlServer);
		while (re.next()) {
			int id = re.getInt("id");
			String title = re.getString("title");
			
			prepst.setInt(1, id);
			prepst.setString(2, title);
			
			prepst.executeUpdate();
		}
	}
	public static void main(String[] args) throws SQLException {
		convertCategoryTable();
	}
}
